﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPagerBinaryTopMax]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetPagerBinaryTopMax]

GO
/***********************************************
***功能描述：	二分法分页排序通用存储过程 。
***作者：周正文
***建立时间:2010-10-21
***参数说明：
    @Table 表名，要查询的表名。可以是视图，带join 的子句。表名，无需带 from 关键字。注意 当使用带join 的子句。带主键的表的别名必须是A
			如
			@Table=
			#TempVipTime AT
            INNER JOIN CompanyInfo A on AT.CompanyID=A.CompanyID
            inner Join CompanyMst C on AT.CompanyID = C.CompanyID and C.DeleteFlag=''false'' ,@PrimaryId ='A.CompanyID' CompanyID在CompanyInfo中 
            因此别名必须是A
            
    @CountTable 取得满足条件的结果总数时，应该查询的表明。有时没有提高查询速度，可以省去不必要的join ，有默值 ， @CountTable=@Table 可以不传
    @Fields 要查询出来字段名。如果查询所有字段，使用 *
    @WhereClause 查询条件，不带 where 关键字
    @PrimaryId 要查询主表的主键名称。唯一标识。内部 查询Min，Max值是以@PrimaryId为准
    @SortBy    @SortBy 只能指定@PrimaryId desc or  @PrimaryId asc 此排序方法是通过 Max,Min,Top 来确定分页的边界。因此必须与主键一致
    @PageSize 分页大小
    @PageIndex 页码
    @PageCount 总页数 输出参数
    @RecordCount 总记录数 输出参数 ，如果>0 的数 内部将不会进行统计了，避免重复统计
    
***示例：
   如查询按分页 SAD 有所有记录
   
   DECLARE	@return_value int,
		@PageIndex int,
		@PageCount int,
		@RecordCount int,
		@WhereClause varchar(max)
   EXEC	@return_value = [dbo].[GetPagerBinaryTopMax]
		@Table = N'SAD',
		@Fields = N'*',
		@WhereClause = ' 1=1 ',
		@PrimaryId = N'SADID',
		@SortBy = N'SADID DESC',
		@PageSize = 500,
		@PageIndex = @PageIndex OUTPUT,
		@PageCount = @PageCount OUTPUT,
		@RecordCount = @RecordCount OUTPUT

   SELECT	@PageIndex as N'@PageIndex',
		@PageCount as N'@PageCount',
		@RecordCount as N'@RecordCount'

   SELECT	'Return Value' = @return_value    
****************************************************/
CREATE PROC [dbo].[GetPagerBinaryTopMax]
    (
      @Table NVARCHAR(max) ,
      @Fields NVARCHAR(max) ,
      @WhereClause NVARCHAR(max) ,
      @PrimaryId NVARCHAR(128) , 
      @SortBy NVARCHAR(128) = @PrimaryId ,
      @CountTable NVarchar(max)=@Table,
      @PageSize INT ,
      @PageIndex INT OUTPUT ,
      @PageCount INT = 0 OUTPUT ,
      @RecordCount INT = 0 OUTPUT
    )
AS 
   Set NoCount ON;
    
    IF @Fields = '' 
        SET @Fields = '*'
    IF @WhereClause = '' 
        SET @WhereClause = ' 1=1 ' ;
    IF @SortBy = '' 
        SET @SortBy = @PrimaryId ;

    --排序标识 true 为正序，false 为反序
    DECLARE @SortFlag BIT ;
    DECLARE @Descending VARCHAR(128) ;
    DECLARE @Ascending VARCHAR(128) ;	
--确定是排序标识，升序字段，降序字段    
    IF ( CHARINDEX(' desc', @SortBy) > 0 ) 
        BEGIN
            SET @Ascending = REPLACE(@SortBy, 'desc', '') ;
            SET @Descending = @SortBy ;
            SET @SortFlag = 'false' ; 
        END
    ELSE 
        BEGIN
            SET @Descending = @SortBy + ' desc' ;
            SET @Ascending = @SortBy ;
            SET @SortFlag = 'true' ; 
        END
   
	--查询 sql字串声明;
    DECLARE @Sql NVARCHAR(MAX) ;
    
    IF  @RecordCount <= 0 or  @RecordCount IS NULL
        BEGIN
			  --计算记录数,Sql 字串声明
            DECLARE @SqlCount NVARCHAR(1024) ; 
            DECLARE @ParmDefinition NVARCHAR(128) ;
   
            SET @ParmDefinition = N'@Count int OUTPUT'
            SET @SqlCount = 'SELECT @Count=COUNT(0) FROM ' + @CountTable
                + ' where ' + @WhereClause ;
			 --取得总记录数

            EXECUTE sp_executesql @SqlCount, @ParmDefinition,
                @Count = @RecordCount OUTPUT

        END
    DECLARE @LastCount INT ; --最后一页的数量,为0表示 恰好是整页
    DECLARE @PageNumber INT ;--页数
    DECLARE @InvertedPageIndex INT ; 
    SET @PageNumber = @RecordCount / @PageSize ;
    SET @LastCount = @RecordCount % @PageSize ;
    --Print @LastCount
    IF @LastCount > 0 
        SET @PageNumber = @PageNumber + 1 ;
    SET @PageCount = @PageNumber ;
    IF @PageNumber = 0 
        BEGIN
            SET @PageIndex = 1
        END
    ELSE 
        BEGIN
            IF @PageIndex <= 0 
                SET @PageIndex = 1 ;
            IF @PageIndex > @PageNumber 
                SET @PageIndex = @PageNumber ;
        END
    
    DECLARE @MaxSql NVARCHAR(max) ;
    DECLARE @MinSql NVARCHAR(max) ;
    DECLARE @TopCount INT ;
    IF @PageIndex <= 2
        OR @PageIndex <= @PageNumber / 2 + @PageNumber % 2 --在前半段
        BEGIN
            IF @PageIndex = 1 --PageNumber=PageIndex
                BEGIN
                  
                    SET @Sql = 'Select Top ' + CAST(@PageSize AS VARCHAR(10))
                        + ' ' + @Fields + ' From ' + @Table + ' where '
                        + @WhereClause + ' Order by ' + @SortBy ;
                END
            ELSE 
                BEGIN
                    SET @TopCount = ( @PageIndex - 1 ) * @PageSize ;
                   
                    IF @SortFlag = 'true'--正序 人小到大
                        BEGIN
                            SET @MaxSql = 'Select Max(' + @PrimaryId
                                + ') From ( Select Top '
                                + CAST(@TopCount AS VARCHAR(10)) + ' '
                                + @PrimaryId + ' from ' + @Table + ' where '
                                + @WhereClause + 'Order by ' + @Ascending
                                + ' ) as A' ;
                            SET @Sql = 'Select Top '
                                + CAST(@PageSize AS VARCHAR(10)) + ' '
                                + @Fields + ' From ' + @Table + ' where '
                                + @WhereClause + ' and ' + @PrimaryId + '>('
                                + @MaxSql + ' ) Order by ' + @Ascending ;
                                 
                        END
                    ELSE 
                        BEGIN
                            SET @MinSql = 'Select Min(' + @PrimaryId
                                + ') From ( Select Top '
                                + CAST(@TopCount AS VARCHAR(10)) + ' '
                                + @PrimaryId + ' from ' + @Table + ' where '
                                + @WhereClause + 'Order by ' + @Descending
                                + ' ) as A' ;
                            SET @Sql = 'Select Top '
                                + CAST(@PageSize AS VARCHAR(10)) + ' '
                                + @Fields + ' From ' + @Table + ' where '
                                + @WhereClause + ' and ' + @PrimaryId + '< ('
                                + @MinSql + ' ) Order by ' + @Descending ;
                        END
                END
        END
    ELSE 
        BEGIN
            SET @InvertedPageIndex = @PageCount - @PageIndex + 1 ;--倒过来数是第几页
            IF @InvertedPageIndex = 1--最后一页
                BEGIN
                    IF @LastCount > 0 
                        SET @TopCount = @LastCount ;
                    ELSE 
                        SET @TopCount = @PageSize ; --最后一页恰好是整页
                    IF @SortFlag = 'true' 
                        BEGIN
                            SET @MaxSql = 'Select Top '
                                + CAST(@TopCount AS VARCHAR(10)) + ' '
                                + @Fields + ' from ' + @Table + ' where '
                                + @WhereClause + ' order by  ' + @Descending ;
                            SET @Sql = 'select * from ( '
                                + @MaxSql + ') A order by ' + @Ascending ;
                        END
                    ELSE 
                        BEGIN
                            SET @MaxSql = 'Select Top '
                                + CAST(@TopCount AS VARCHAR(10)) + ' '
                                + @Fields + ' from ' + @Table + ' where '
                                + @WhereClause + ' order by  ' + @Ascending ;
                            SET @Sql = 'select * from ( '
                                + @MaxSql + ') A order by ' + @Descending ;
                        END
                END
            ELSE 
                BEGIN
                    SET @TopCount = ( @InvertedPageIndex - 1 ) * @PageSize ;
                    IF @LastCount > 0 
                        SET @TopCount = @TopCount - ( @PageSize - @LastCount ) ;
                    IF @SortFlag = 'true' 
                        BEGIN
                            SET @MinSql = 'Select min(' + @PrimaryId
                                + ' ) from ( Select Top '
                                + CAST(@TopCount AS VARCHAR(10)) + ' '
                                + @PrimaryId + ' from ' + @Table + ' where '
                                + @WhereClause + ' order by ' + @Descending
                                + ') A' ;
                            SET @Sql = 'Select * from (Select Top '
                                + CAST(@PageSize AS VARCHAR(10)) + ' '
                                + @Fields + ' from ' + @Table + ' where '
                                + @PrimaryId + '< (' + @MinSql + ') and '
                                + @WhereClause + ' order by ' + @Descending
                                + ') B' + ' Order by ' + @Ascending ;
                        END
                    ELSE 
                        BEGIN
                            SET @MaxSql = 'Select max(' + @PrimaryId
                                + ' ) from ( Select Top '
                                + CAST(@TopCount AS VARCHAR(10)) + ' '
                                + @PrimaryId + ' from ' + @Table + ' where '
                                + @WhereClause + ' order by ' + @Ascending
                                + ') A' ;
                            SET @Sql = 'Select * from (Select Top '
                                + CAST(@PageSize AS VARCHAR(10)) + ' '
                                + @Fields + ' from ' + @Table + ' where '
                                + @PrimaryId + '> (' + @MaxSql + ') and '
                                + @WhereClause + ' order by ' + @Ascending
                                + ') B' + ' Order by ' + @Descending ;
                        END
                END
        END
	--PRINT @Sql ;
    EXEC(@Sql) ;
    


GO


